package com.house.java.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.house.java.dao.NewsDAO;
import com.house.java.model.News;
import com.house.java.model.Paging;
import com.house.java.util.DBUtil;

public class NewsDaoImpl implements NewsDAO {
	
	private static Logger log = Logger.getLogger(NewsDaoImpl.class);

	public List<News> queryNewsByCategory(Integer countryCode,
			Integer provinceCode, Integer cityCode, Paging paging) {
		List<News> list = new ArrayList<News>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from news where 1=1 ");
		StringBuffer sbCount = new StringBuffer();
		sbCount.append("select count(*) from news where 1=1 ");
		int index = 0;
		if(null != countryCode){
			sb.append(" and NEWS_COUNTRY_CODE = ?");
			sbCount.append(" and NEWS_COUNTRY_CODE = ?");
			index++;
		}
		if(null != provinceCode){
			sb.append(" and NEWS_PROVINCE_CODE = ?");
			sbCount.append(" and NEWS_PROVINCE_CODE = ?");
			index++;
		}
		if(null != cityCode){
			sb.append(" and NEWS_CITY_CODE = ?");
			sbCount.append(" and NEWS_CITY_CODE = ?");
			index++;
		}
		if(paging != null){
			sb.append(" limit ?,?");
			index++;
			index++;
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			if(index > 0){
				int paramsIndex = 1;
				
				if(null != countryCode){
					pst.setInt(paramsIndex, countryCode);
					pstCount.setInt(paramsIndex, countryCode);
					paramsIndex++;
				}
				if(null != provinceCode){
					pst.setInt(paramsIndex, provinceCode);
					pstCount.setInt(paramsIndex, provinceCode);
					paramsIndex++;
				}
				if(null != cityCode){
					pst.setInt(paramsIndex, cityCode);
					pstCount.setInt(paramsIndex, cityCode);
					paramsIndex++;
				}
				if(paging != null){
					pst.setInt(paramsIndex++, (paging.getCurrentPage()-1)*paging.getPageSize());
					pst.setInt(paramsIndex++, paging.getPageSize());
					buildPaging(pstCount, paging);
				}
			}
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			News news;
			while(result.next()){
				news = new News();
				news.setId(result.getLong("ID"));
				news.setAuthor(result.getString("NEWS_AUTHOR"));
				news.setBody(result.getString("NEWS_BODY"));
				news.setCityCode(result.getInt("NEWS_COUNTRY_CODE"));
				news.setProvinceCode(result.getInt("NEWS_PROVINCE_CODE"));
				news.setCityCode(result.getInt("NEWS_CITY_CODE"));
				news.setDate(result.getDate("NEWS_DATE"));
				news.setDigest(result.getString("NEWS_DIGEST"));
				news.setMainCategory(result.getInt("NEWS_MAIN_CATEGORY"));
				news.setSecondCategory(result.getInt("NEWS_SECOND_CATEGORY"));
				news.setSource(result.getString("NEWS_SOURCE"));
				news.setTitle(result.getString("NEWS_TITLE"));
				news.setImages(result.getString("IMAGES"));
				list.add(news);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return list;
	}

	
	
	private static void logConnectionCloseExceptionFunction(){
		log.error(Thread.currentThread().getId()+" close Connection appear an Exception");
	}
	private static void logConnectionRollbackException(){
		log.error(Thread.currentThread().getId()+" rollback Connection appear an Exception");
	}
	
	/**
	 * 适用于  select count(*) 形式的SQL
	 * @param pst
	 * @param paging
	 */
	private void buildPaging(PreparedStatement pst,Paging paging){
		try {
			ResultSet rs = pst.executeQuery();
			paging.setTotalSize(rs.next()?rs.getInt(1):0);
			log.info(pst.toString());
			rs.close();
			pst.close();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
	}


	/**
	 * 添加新闻
	 * 
	 */
	public void addNews(News news) {
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "insert into new_house(" +
			"NEWS_AUTHOR,NEWS_BODY,NEWS_COUNTRY_CODE,NEWS_PROVINCE_CODE,NEWS_CITY_CODE," +
			"NEWS_DATE,NEWS_DIGEST,NEWS_MAIN_CATEGORY,NEWS_SECOND_CATEGORY,NEWS_SOURCE," +
			"NEWS_TITLE,IMAGES)" +
			" values(?,?,?,?,? ,?,?,?,?,? ,?,?)";
			PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			int parameterIndex = 1;
			
			pst.setString(parameterIndex++, news.getAuthor());
			pst.setString(parameterIndex++, news.getBody());
			pst.setInt(parameterIndex++, news.getCountryCode());
			pst.setInt(parameterIndex++, news.getProvinceCode());
			pst.setInt(parameterIndex++, news.getCityCode());
			
			pst.setDate(parameterIndex++, news.getDate());
			pst.setString(parameterIndex++, news.getDigest());
			pst.setInt(parameterIndex++, news.getMainCategory());
			pst.setInt(parameterIndex++, news.getSecondCategory());
			pst.setString(parameterIndex++, news.getSource());
			
			pst.setString(parameterIndex++, news.getTitle());
			pst.setString(parameterIndex++, news.getImages());
			log.error("parameterIndex ---> "+parameterIndex);
			pst.executeUpdate();
			// 获取刚刚插入的记录的主键，设置到house对象中
			ResultSet resultSet = pst.getGeneratedKeys();
			if(resultSet.next()){
				Long id = resultSet.getLong(1);
				news.setId(id);
			}
			resultSet.close();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
	}



	public int delete(Long id) {
		int result = -1;
		Connection conn = DBUtil.getConnection();
		try {
			if(conn.getAutoCommit()){
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			log.error("获取数据库连接是否自动提交发生异常");
		}
		try {
			String sql = "delete from news where id = ? ";
			PreparedStatement pst = conn.prepareStatement(sql);
			int parameterIndex = 1;
			pst.setLong(parameterIndex, id);
			// 获取刚刚插入的记录的主键，设置到house对象中
			result = pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			log.error(e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logConnectionRollbackException();
				log.error(Thread.currentThread().getId()+" "+e1.getMessage());
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logConnectionCloseExceptionFunction();
				log.error(Thread.currentThread().getId()+" "+e.getMessage());
			}
		}
		return result;
	}



	public List<News> queryById(Long id) {
		List<News> list = new ArrayList<News>();
		Connection conn = DBUtil.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from news where 1=1 ");
//		StringBuffer sbCount = new StringBuffer();
//		sbCount.append("select count(*) from news where 1=1 ");
		if(null != id){
			sb.append(" and id=? ");
//			sbCount.append(" and id=? ");
		}
		try {
			PreparedStatement pst = conn.prepareStatement(sb.toString());
//			PreparedStatement pstCount = conn.prepareStatement(sbCount.toString());
			if(null != id){
				pst.setLong(1, id);
//				pstCount.setLong(1, id);
			}
			log.info(pst.toString());
			ResultSet result = pst.executeQuery();
			News news;
			while(result.next()){
				news = new News();
				news.setId(result.getLong("ID"));
				news.setAuthor(result.getString("NEWS_AUTHOR"));
				news.setBody(result.getString("NEWS_BODY"));
				news.setCityCode(result.getInt("NEWS_COUNTRY_CODE"));
				news.setProvinceCode(result.getInt("NEWS_PROVINCE_CODE"));
				news.setCityCode(result.getInt("NEWS_CITY_CODE"));
				news.setDate(result.getDate("NEWS_DATE"));
				news.setDigest(result.getString("NEWS_DIGEST"));
				news.setMainCategory(result.getInt("NEWS_MAIN_CATEGORY"));
				news.setSecondCategory(result.getInt("NEWS_SECOND_CATEGORY"));
				news.setSource(result.getString("NEWS_SOURCE"));
				news.setTitle(result.getString("NEWS_TITLE"));
				news.setImages(result.getString("IMAGES"));
				list.add(news);
			}
			result.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}finally{
			try {
				conn.close();
			} catch (Exception e2) {
				logConnectionCloseExceptionFunction();
				log.error(e2.getMessage());
			}
		}
		return list;
	}
}
